//
//  XTStatusDAL.swift
//  Weibo(Swift)
//
//  Created by 肖霆 on 16/5/25.
//  Copyright © 2016年 肖霆. All rights reserved.
//

import UIKit

class XTStatusDAL: NSObject {
    
        //  MARK: -为首页加载数据-
    class func loadData(maxId:Int64,sinceId:Int64,callBack:(status:[[String:AnyObject]])->()) {
                //cache:存储
        var tempArray = [[String:AnyObject]]()
        //检索本地是否有缓存数据
        let result = checkCacheData(maxId, sinceId: sinceId)
        if result.count>0{
            callBack(status: result)
        }
        //请求网络数据
        XTNetWorkTools.sharedTools.requestStatus(XTUserAccountViewModel.sharedUserAccount.accessToken!, maxId: maxId, sinceId: sinceId) { (response, error) -> () in
            if error != nil {
                print("网络请求异常")
                callBack(status: tempArray)
                return
            }
            
            guard let result = response as? [String:AnyObject] else{
                print("服务器传回的不是一个JSON格式文件")
                callBack(status: tempArray)
                return
            }
            //*****************注意此处取返回结果中的键值时，一定要注意与数据库中存储的列名完全一致！！！！
            guard let statusArray = result["statuses"] as? [[String:AnyObject]] else{
                print("返回的JSON文件中不含有微博信息")
                callBack(status: tempArray)
                return
            
            }
            
            //将请求的数据缓存到本地
            cacheData(statusArray)
            //返回数据给首页进行显示
            callBack(status: statusArray)
        }
        
    }
        //  MARK: -检查本地是否有缓存的数据的方法-
//    class func checkCacheData(maxId:Int64,sinceId:Int64) -> [[String:AnyObject]]{
//        
//        var sql = "SELECT statusid,status,userid FROM Status\n"
//        if maxId > 0 {
//            sql += "WHERE statusid < \(maxId)\n"
//        }else{
//            sql += "WHERE statusid > \(sinceId)\n"
//        }
//        sql += "AND userid = \(XTUserAccountViewModel.sharedUserAccount.userAccount?.uid)\n"
//        sql += "ORDER BY statusid desc\n"
//        sql += "LIMIT 20\n"
//        let result = SqliteManager.manager.queryResultWithSql(sql)
//        var tempArray = [[String:AnyObject]]()
//        for value in result{
//            let statusData = value["status"]! as! NSData
//            let statusDic = try! NSJSONSerialization.JSONObjectWithData(statusData, options: []) as! [String:AnyObject]
//            tempArray.append(statusDic)
//        }
//    return tempArray
//    }
    
    
    class func checkCacheData(maxId: Int64, sinceId: Int64) -> [[String: AnyObject]] {
        
        //SELECT * FROM Status where statusid > 3978603153081176 and userid = 1800530611 order by statusid desc limit 20
        
        var sql = "SELECT statusid, status, userid FROM Status\n"
        if maxId > 0 {
            sql += "where statusid < \(maxId)\n"
        } else {
            sql += "where statusid > \(sinceId)\n"
        }
        sql += "and userid = \(XTUserAccountViewModel.sharedUserAccount.userAccount!.uid)\n"
        //  排序方式
        sql += "order by statusid desc\n"
        //  返回多少记录
        sql += "limit 20\n"
        
        let result = SqliteManager.manager.queryResultWithSql(sql)
        
        var tempArray = [[String: AnyObject]]()
        
        for value in result {
            //  取到微博数据转字典
            let statusData = value["status"]! as! NSData
            let statusDic =  try! NSJSONSerialization.JSONObjectWithData(statusData, options: []) as! [String: AnyObject]
            tempArray.append(statusDic)
        }
        
        
        return tempArray
    }

        //  MARK: -将网络请求回来的数据保存在本地的方法-
    class func cacheData(status:[[String:AnyObject]]){
    
        let sql = "INSERT INTO STATUS(STATUSID, STATUS, USERID) VALUES (?,?,?)"
        let userid = XTUserAccountViewModel.sharedUserAccount.userAccount?.uid
        
        SqliteManager.manager.queue?.inTransaction({ (db, rollback) in
            //执行sql语句
            for value in status{
                let statusid = value["id"]
                //将字典转换为JSONdata
                let statusData = try!NSJSONSerialization.dataWithJSONObject(value, options: []);
                let result = db.executeUpdate(sql, withArgumentsInArray: ["\(statusid)",statusData,"\(userid)"])
                
                if result == false{
                    //如果向数据库中添加数据失败，则让数据库进行数据回滚
                    rollback.memory = true
                    break
                }
            }
        })
        
            
            
        }
        
    
        //  MARK: -自动清除缓存-
    class func cleanCacheData(){
        
        let date = NSDate().dateByAddingTimeInterval(-60 * 30)
        
        let dateFormatter = NSDateFormatter()
        dateFormatter.dateFormat = "yyyy-MM-dd HH:mm:ss"
        dateFormatter.locale = NSLocale(localeIdentifier: "en_US")
        let dateStr = dateFormatter.stringFromDate(date)
        
        let sql = "DELETE FROM STATUS WHERE TIME < '\(dateStr)'"
        
        SqliteManager.manager.queue?.inDatabase({ (db) in
            let result = db.executeUpdate(sql, withArgumentsInArray: nil)
            
            if result{
                print("清除缓存成功\(db.changes())")
            }else{
                print("清除缓存失败")
            }
        })
        
        
    }
}
